/*********************************************************************************
 *      Copyright:  (C) 2021 weizhiyan<1933283357@qq.com>
 *                  All rights reserved.
 *
 *       Filename:  database.c
 *    Description:  This file 
 *                 
 *        Version:  1.0.0(08/02/2021)
 *         Author:  weizhiyan <1933283357@qq.com>
 *      ChangeLog:  1, Release initial version on "08/02/2021 06:33:42 PM"
 *                 
 ********************************************************************************/


#include <stdio.h>
#include <sqlite3.h>
#include <stdlib.h>
#include <time.h>
#include <string.h>
#include <errno.h>

#include "zlog.h"

char *err = NULL;


/* open database and initial */
int open_database(sqlite3 **db, char dbfile[64])
{
    int rv = 0;
    int result;

    rv = sqlite3_open(dbfile,db);
    if(rv != SQLITE_OK)
    {
        dzlog_error("open database failure!\n");
        sqlite3_close(*db);
        return -1;
    }   

    dzlog_info("open save_database successfully!\n");

    const char *sql="create table if not exists rpi(id char[50],temp double,tm int)";
    result= sqlite3_exec(*db,sql,NULL,NULL,&err);
    if(result != SQLITE_OK)
    {
        dzlog_error("open table failure!\n");
        sqlite3_close(*db);
        return -2;
    }
    dzlog_info(" table already exists!\n");

    return rv;

}

/* Gets several pieces of data in the database */
int get_table(sqlite3 *db)
{
    const  char *str="select * from rpi";
    int    nRow;
    int    nCol;
    char   **Result;
    int    result;
    char   *errmsg = NULL;

    result=sqlite3_get_table(db,str,&Result,&nRow,&nCol,&errmsg);
    if(NULL != errmsg)
    {
        dzlog_error("get data from database  failure: %s\n",strerror(errno));
        sqlite3_free_table(Result);
        return -2;
    }
    return nRow;
}



/* Insert data into the database */
int insert_data(sqlite3 *db,time_t tm,char *devid,double temp)
{
    int te;
    te=(int)tm;
    int result;
    const char *sqli= sqlite3_mprintf("INSERT INTO rpi VALUES(' %s',' %f',' %d' );", devid, temp, te);
    result= sqlite3_exec(db,sqli,NULL,NULL,&err);
    if(result != SQLITE_OK)
    {
        dzlog_error("insert data failure: %s\n",strerror(errno));
        sqlite3_close(db);
        return -3;
    }
    dzlog_info("insert data successfully!\n");
}

/* Get the data from the database */
int get_data(sqlite3 *db,char *devid,int *tm,double *temp,int *rowid,int size_devid)
{

    sqlite3_stmt      *pstmt = 0;
    int                rv = 0;
    int                rv1;
    int                rv2;


    /*Take the first piece of data from the database each time */
    const char *arr ="select * from rpi limit 1;";
    /* Take the first piece of rowid from the database each time */
    const char *row = "select rowid from rpi limit 1;";
    rv = sqlite3_prepare_v2(db,arr,strlen(arr),&pstmt,NULL);
    if(rv != SQLITE_OK)
    {
        dzlog_error("prepare failure: %s\n",strerror(errno));
        return -4;
    }
    rv1 = sqlite3_step(pstmt);
    if(rv1 == SQLITE_DONE)
    {
        dzlog_info("get data from database successfully!\n");
    }

    strncpy(devid,(char *)sqlite3_column_text(pstmt,0),size_devid);

    /* Take the data for the first column of the first row */
    *temp = sqlite3_column_double(pstmt,1);

    *tm = sqlite3_column_int(pstmt,2);

    rv = sqlite3_prepare_v2(db,row,strlen(arr),&pstmt,NULL);
    sqlite3_step(pstmt);
    *rowid = sqlite3_column_int(pstmt,0);

    sqlite3_finalize(pstmt);
}

/* delete data from database */
int delete_data(sqlite3 *db,int rowid)
{
    int result;
    char deldata[1024];
    snprintf(deldata, sizeof(deldata),  "delete from rpi where rowid = %d;", rowid);
    result= sqlite3_exec(db,deldata,NULL,NULL,&err);
    if(result != SQLITE_OK)
    {
        dzlog_error("delete data failure: %s\n",strerror(errno));
        sqlite3_close(db);
        return -2;
    }
    else
    {
        dzlog_notice("delete data from database successfully!\n");
    }

}
